﻿/****** Object:  Table [dbo].[SVNRepository]    Script Date: 01/13/2009 10:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SVNRepository](
	[repositoryID] [int] IDENTITY(1,1) NOT NULL,
	[repositoryURL] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[repositoryKey] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[repositoryServerURL] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_SVNRepository] PRIMARY KEY CLUSTERED 
(
	[repositoryID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

INSERT INTO [dbo].[SVNRepository]
           ([repositoryURL]
           ,[repositoryKey]
           ,[repositoryServerURL])
     VALUES
           ('http://THIS_SERVER/svn/src_code/'
           ,'src_code'
           ,'http://THIS_SERVER/svn/')

INSERT INTO [dbo].[SVNRepository]
           ([repositoryURL]
           ,[repositoryKey]
           ,[repositoryServerURL])
     VALUES
           ('http://THIS_SERVER/svn/com_code/'
           ,'com_code'
           ,'http://THIS_SERVER/svn/')
GO


/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.SVNLog ADD
	repositoryID int NOT NULL DEFAULT('1'),
	logID int NOT NULL IDENTITY (1, 1)
GO
COMMIT





/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.SVNLog ADD CONSTRAINT
	FK_SVNLog_SVNRepository FOREIGN KEY
	(
	repositoryID
	) REFERENCES dbo.SVNRepository
	(
	repositoryID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.SVNLogRevisionReviews ADD
	logID int NULL
GO
COMMIT



/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
UPDATE SVNLogRevisionReviews
SET SVNLogRevisionReviews.logID = SVNLog.logID
FROM SVNLog
INNER JOIN SVNLogRevisionReviews
ON SVNLogRevisionReviews.revision = SVNLog.revision

GO
COMMIT



/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.SVNLogRevisionReviews
	DROP CONSTRAINT FK_SVNLogRevisionReviews_Reviews
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.SVNLogRevisionReviews
	DROP CONSTRAINT FK_SVNLogRevisionReviews_SVNLog
GO
ALTER TABLE dbo.SVNLog
	DROP CONSTRAINT PK_SVNLog
GO
ALTER TABLE dbo.SVNLog ADD CONSTRAINT
	PK_SVNLog PRIMARY KEY NONCLUSTERED 
	(
	logID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_SVNLogRevisionReviews
	(
	revision int NOT NULL,
	reviewID int NOT NULL,
	revisionReviewID int NOT NULL IDENTITY (1, 1),
	logID int NOT NULL
	)  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_SVNLogRevisionReviews ON
GO
IF EXISTS(SELECT * FROM dbo.SVNLogRevisionReviews)
	 EXEC('INSERT INTO dbo.Tmp_SVNLogRevisionReviews (revision, reviewID, revisionReviewID, logID)
		SELECT revision, reviewID, revisionReviewID, logID FROM dbo.SVNLogRevisionReviews WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_SVNLogRevisionReviews OFF
GO
DROP TABLE dbo.SVNLogRevisionReviews
GO
EXECUTE sp_rename N'dbo.Tmp_SVNLogRevisionReviews', N'SVNLogRevisionReviews', 'OBJECT' 
GO
ALTER TABLE dbo.SVNLogRevisionReviews ADD CONSTRAINT
	PK_SVNLogRevisionReviews PRIMARY KEY NONCLUSTERED 
	(
	revisionReviewID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.SVNLogRevisionReviews ADD CONSTRAINT
	FK_SVNLogRevisionReviews_Reviews FOREIGN KEY
	(
	reviewID
	) REFERENCES dbo.Reviews
	(
	reviewID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.SVNLogRevisionReviews ADD CONSTRAINT
	FK_SVNLogRevisionReviews_SVNLog1 FOREIGN KEY
	(
	logID
	) REFERENCES dbo.SVNLog
	(
	logID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT


/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.ReviewFiles ADD
	repositoryID int NOT NULL CONSTRAINT DF_ReviewFiles_repositoryID DEFAULT 1
GO
ALTER TABLE dbo.ReviewFiles
	DROP COLUMN baseURL, repoName
GO
COMMIT

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.SVNLogRevisionReviews
	DROP COLUMN revision
GO
COMMIT

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.ReviewComments ADD
	defectNumber int NULL
GO
COMMIT

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.ReviewFileComments ADD
	defectNumber int NULL
GO
COMMIT
